use crate::duck::connector::get_connection;
use crate::error::Result;
use crate::schemas::duck::{DuckData, FieldItem};
use duckdb::params;
use duckdb::types::Value as DuckValue;
use serde_json::{json, Map, Value};

fn value_to_json(value: DuckValue) -> Value {
    match value {
        DuckValue::Null => Value::Null,
        DuckValue::Boolean(b) => json!(b),
        DuckValue::TinyInt(i) => json!(i),
        DuckValue::SmallInt(i) => json!(i),
        DuckValue::Int(i) => json!(i),
        DuckValue::BigInt(i) => json!(i),
        DuckValue::Float(f) => json!(f),
        DuckValue::Double(f) => json!(f),
        DuckValue::Text(s) => json!(s),
        DuckValue::Timestamp(_, b) => json!(b),
        // 添加其他类型的处理...
        _ => Value::Null,
    }
}

fn query_by_sql(sql: &str) -> Result<DuckData> {
    let conn = get_connection()?;
    let mut stmt = conn.prepare(sql)?;
    stmt.execute([])?; // 先执行查询
    let cols: Vec<String> = stmt.column_names();
    let mut rows = stmt.query([])?;
    let mut data: Vec<Value> = vec![];

    while let Some(row) = rows.next()? {
        let mut one = Map::new();
        for (i, col) in cols.iter().enumerate() {
            let v: DuckValue = row.get(i)?;
            one.insert(col.clone(), value_to_json(v.clone()));
        }
        data.push(one.into());
    }

    // 5. 包装为结构体并序列化
    let csv_data = DuckData { result: data };
    Ok(csv_data)
}

pub fn table_all(table_name: &String) -> Result<DuckData> {
    query_by_sql(&format!(r#"SELECT * FROM "{}""#, table_name))
}

pub fn table_exist(table_name: &String) -> Result<bool> {
    let conn = get_connection()?;
    let query = "SELECT COUNT(*) FROM information_schema.tables WHERE table_name = ?";
    let count: i64 = conn.query_row(query, &[table_name], |row| row.get(0))?;
    Ok(count > 0)
}

pub fn field_all(ds_id: &String) -> Result<Vec<FieldItem>> {
    let conn = get_connection()?;

    let mut stmt = conn.prepare(
        r#"
        SELECT
            column_name,
            data_type,
            is_nullable
        FROM
            information_schema.columns
        WHERE
            table_name = ?
        ORDER BY
            ordinal_position;
        "#,
    )?;

    let column_iter = stmt.query_map(params![ds_id], |row| {
        Ok(FieldItem {
            column_name: row.get(0)?,
            data_type: row.get(1)?,
            nullable: row.get(2)?,
        })
    })?;

    let mut columns: Vec<FieldItem> = Vec::new();
    for column in column_iter {
        columns.push(column?);
    }
    Ok(columns)
}
